Case Study: 
Creating a Data Base on Linux
[CountS24]
Vermont State University CIS uses Lemuria as a departmental server.
https://lemuria.cis.vermontstate.edu/
PLEASE NOTE: If Lemuria fails, it will be replaced by Zeta: 
https://zeta.cis.vermontstate.edu/
Lemuria is a Linux machine.

Getting Access to Lemuria:
Professor Chapin is the System Administrator for Lemuria.
(Professor Tookey is familiar with Lemuria also.)
Professor Chapin will give you a user name and password.
For purposes of illustration, we will pretend that ‘iAmAStudent’ is a valid user name, and ‘frenchfry’ is the matching password.  

Copying files to Lemuria:
Many programs can be used to copy files to Lemuria.
We will illustrate FileZilla.
https://filezilla-project.org/download.php

After you get FileZilla installed on your system (ask for help if you need it) run it.
(See illustrations.)
HOST: sftp://lemuria.cis.vtc.edu
USERNAME: iAmAStudent
PASSWORD: french-fry
PORT:22
You can copy and paste into those fields.

Hit Quickconnect.
The third picture is FileZilla connected to account ‘ktookey’.

Once FileZilla is connected to both your computer and to Lemuria,
You can drag files back and forth between the two computers.
NOTES:
If you go to replace an existing file, it will prompt you to make sure you want to overwrite it.

Please notice the directory public_html on Lemuria, account ‘ktookey’.
Anything placed in that directory becomes publicly accessible via 
http://lemuria.cis.vermontstate.edu/~ktookey
(If you are in a different account, change the account name.)
NOTE: You may have to change permissions to allow access.
SEE for example
 https://www.atlantic.net/vps-hosting/how-to-use-chmod-change-mode-command-in-linux/

Lemuria can be Putty in your hands
You will almost certainly need to actually log in to the Lemuria server, if only to change permissions.  (We will discuss creation of a database later.)

Putty is an open source SSH and telnet client, available at:
https://putty.org/
Once it is downloaded and installed on your system, you can use it to actually log on to Lemuria.
HOST NAME: lemuria.cis.vermontstate.edu
PORT: 22
Click OPEN
(See illustrations)

Once you run Putty, it will try to log you in:

Type ‘iAmAStudent’ or whatever your user ID is.
(Mine is ‘ktookey’)

It will prompt you for your password.
PLEASE NOTE:
If you COPY text, you can paste it into the Putty window by using RIGHT-CLICK. (This can be very handy when typing your password, but even handier when you need to type large numbers of SQL commands.)

If you type the password correctly, it will let you in.

Some of my most commonly used LINUX command include:
cd
ls
chmod
If you are not at all familiar with Linux commands, you should have taken at least Operating Systems first.  ASK FOR HELP.

Creating a PostGRE SQL (PSQL) Database on Lemuria

Quick Reference Guide:
createdb database		//creates a database
dropdb database		//disposes of a databasee
psql database		//use a database
ALTER ROLE iAmAStudent WITH PASSWORD ‘whatever’;
				//associate the password ‘whatever’
				//owned by iAmAStudent
				//with the current database

Now we will attempt to create a VERY simple database.
(It would be a good idea to locate a good source to learn about SQL)
createdb CountS24
psql -d CountS24
ALTER ROLE ktookey WITH PASSWORD 'frenchfry';
CREATE TABLE Counter(
        count          NUMERIC(3) PRIMARY KEY
	);
SELECT count FROM Counter;
INSERT INTO Counter VALUES (0);
UPDATE Counter SET count = count + 1;
SELECT count FROM Counter;
UPDATE Counter SET count = count - 1;
SELECT count FROM Counter; 
\q

Create a new database titled ‘CountS24’

Use the PSQL database CountS24

Give the database a password, so others can use it.
NOTE: If you paste it from WORD, you may get a syntax error,
since WORD loves to replace 'frenchfry'; with ‘frenchfry’;

This database will have one table, with one attribute.
(Have you found a good source for SQL yet?)

I wonder what the count starts at?

OOPS, we should have an initial value.

Now that we have a value, we can update it:

Besides adding, you can subtract:

This is pretty silly for a database.  Not only is there only one table with only one attribute, but the database assumes there is only one ROW in the table.
The only purpose of this is to walk you through the steps for creating a database.

When done with the database, you can QUIT

(If you walk away from your computer, Putty will crash and need to be restarted.)

Using PHP to manipulate a Database

Advanced Web Design introduces how to use PHP to manipulate a database.
We will show how to use PHP to manipulate our CountS24.

See PHPpartI and PHPpartII for help getting started.
There are many resources available on the Internet.

Assume your PHP code is in a file with the extension *.php.
PLEASE NOTE:
Most PHP code will not work unless it is on a server, such as Lemuria.

PHP code is encased within:
<?php …. ?>
/* …. */ and // work for commenting
echo “Echo can be used for debug printing”;
Debugging statements are commented out in the later code.

In order to manipulate a database, you need to know the server it is on, the user who controls it, the name of the database, the password of the database,  and, of course, you need to know what you wish to do with the database.

I check to make sure the SQL code works directly using Putty before I write the PHP code.

Typically, string values are used:
$servername = "localhost";
$username   = "ktookey";
$password   = "frenchfry";
$dbname     = "CountS24";
$query      = "UPDATE Counter SET count = count + 1;";
$query2     = "SELECT * FROM Counter";

NOTE: anyone who examines the PHP source code will know how to modify the database. Better security is beyond the scope of this discussion.

You must, of course CONNECT to the Database.

NOTE: different types of databases have slightly different syntax.
This example, of course, if PostGRESQL on Lemuria.

It is incredibly easy to make a mistake when connecting with the database.
Therefore, PHP requires you to have a “try…catch” to at least report what the error is.

try {
….
}
catch (PDOException $e) {
    print "Error: " . $e->getMessage() . "<br/>";
    die();
}

PDO creates a new connection, which is referred to by the incredibly creative name ‘$connection’.  

$connection = new PDO("pgsql:host=$servername;dbname=$dbname","$username","$password");

In the CountS24 example, of course there is no need to have a loop.
BUT most access to databases produces SETS of data.

The ‘foreach’ command is a loop, which repeats for each row of the result of the query.  In this case, because $query is an UPDATE, there is NO output.
In general, ‘print_r($row);’ prints the entire row;
while ‘print_r($row["count"]);’ only prints the attribute ‘count’ from the row.

foreach($connection->query($query) as $row) 
{
        	print_r($row["count"]);//print_r($row);
    	}

However, the SECOND query ($query2) DOES return the (new) value of ‘count’.

    foreach($connection->query($query2) as $row) 
{
        	print_r($row["count"]);//print_r($row);
	};

When you are done, close the connection:
    $connection = null;

//VERSION with debug prints present but commented out
<?php
//echo "Testing a database connection...";
$servername = "localhost";
$username   = "ktookey";
$password   = "frenchfry";
$dbname     = "CountS24";
$query      = "UPDATE Counter SET count = count + 1;";
$query2     = "SELECT * FROM Counter";
try {
    // Connect and do the deed!
    $connection = new PDO("pgsql:host=$servername;dbname=$dbname","$username","$password");
    //echo "Connected!<br/>";
    foreach($connection->query($query) as $row) {
        print_r($row["count"]);//print_r($row);
    }//no result from the update
    //echo "incremented!<br/>";
    foreach($connection->query($query2) as $row) {
        print_r($row["count"]);//print_r($row);
    }
    //echo "Done!<br/>";
    $connection = null;
}
catch (PDOException $e) {
    print "Error: " . $e->getMessage() . "<br/>";
    die();
}
?>

//addCount.php
<?php
$servername = "localhost";
$username   = "ktookey";
$password   = "frenchfry";
$dbname     = "CountS24";
$query      = "UPDATE Counter SET count = count + 1;";
$query2     = "SELECT * FROM Counter";
try {
    $connection = new PDO("pgsql:host=$servername;dbname=$dbname","$username","$password");
    foreach($connection->query($query) as $row) {
        print_r($row["count"]);
    }
    foreach($connection->query($query2) as $row) {
        print_r($row["count"]);
    }
    $connection = null;
}
catch (PDOException $e) {
    print "Error: " . $e->getMessage() . "<br/>";
    die();
}
?>

A different example will illustrate how parameters can be passed to PHP code.

The opposite version, subtracting from the count, is easy;
Subtract 1, instead of adding one.

//subCount.php
<?php
$servername = "localhost";
$username   = "ktookey";
$password   = "frenchfry";
$dbname     = "CountS24";
$query      = "UPDATE Counter SET count = count + 1;";
$query2     = "SELECT * FROM Counter";
try {
    $connection = new PDO("pgsql:host=$servername;dbname=$dbname","$username","$password");
    foreach($connection->query($query) as $row) {
        print_r($row["count"]);
    }
    foreach($connection->query($query2) as $row) {
        print_r($row["count"]);
    }
    $connection = null;
}
catch (PDOException $e) {
    print "Error: " . $e->getMessage() . "<br/>";
    die();
}
?>

And, if you only want to check the count, you don’t need to either add or subtract.

//checkCount.php
<?php
$servername = "localhost";
$username   = "ktookey";
$password   = "frenchfry";
$dbname     = "CountS24";
$query2     = "SELECT * FROM Counter";
try {
    $connection = new PDO("pgsql:host=$servername;dbname=$dbname","$username","$password");
    foreach($connection->query($query2) as $row) {
        print_r($row["count"]);
    }
    $connection = null;
}
catch (PDOException $e) {
    print "Error: " . $e->getMessage() . "<br/>";
    die();
}
?>

<!—This html file, which can be run from anywhere, has an embedded counter,-->

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Frameset">
<html>
<head>
<title>Dr. Tookey's Counter Web Page</title>
</head>

is the current count.
<embed type="text/html" src="http://lemuria.cis.vtc.edu//~ktookey/counter/checkCount.php" width="80" height="80">
<table>
  <tr>
    <th>OPTION</th>
  </tr>
  <tr>
  <td><a href="http://lemuria.cis.vtc.edu/~ktookey/counter/addCount.php">count up</a></td>
  </tr>
  <tr>
  <td><a href="http://lemuria.cis.vtc.edu/~ktookey/counter/subCount.php">count down</a></td>
  </tr>
  <tr>
  <td><a href="http://~ktookey/counter/checkCount.php">check count</a></td>
  </tr>
</table>
USE BACK button to return here
Use REFRESH to know current count
</html>